﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Security.Principal;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using bitMail.Class;
using bitMail.Forms;
using Microsoft.Win32;
using OfficeOpenXml;

namespace bitMail
{
    public partial class MainWindow
    {
        private const string _excelNoPathLabel = "Brak pliku.";
        private readonly CoreRepository _coreRepo;
        private ICollectionView _defaultView;
        private readonly SqliteHelper _lhelper;
        private DataTable _dt = new DataTable();
        private readonly MailHelper _mailHelper;
        private readonly ConfigHelper _configHelper;
        public string SzablonDoWyslania = "";
        public int SelectedId = 0;
        private readonly BackgroundWorker _worker;
        private int _globalnyNumerZakladki = 0;
        private readonly List<string> _excelCardNames;

        public void GenerateTittle()
        {
            const string day = "15";
            const string month = "03";
            const string year = "16";
            Title = "bitMail " + year + month + day;
        }

        private static bool IsAdministrator()
        {
            return (new WindowsPrincipal(WindowsIdentity.GetCurrent()))
                .IsInRole(WindowsBuiltInRole.Administrator);
        }

        public MainWindow()
        {
            _excelCardNames = new List<string>();
            InitializeComponent();
            _lhelper = new SqliteHelper();
            _mailHelper = new MailHelper();
            _configHelper = new ConfigHelper();
            _lhelper.CreateDbAndTableIfNotExist();
            AktualizujExcelLabel();
            SprobujZaladowacPlikZDomyslnejLokalizacji();
            _worker = new BackgroundWorker();
            _coreRepo = new CoreRepository();
            GenerateTittle();
            //if (!IsAdministrator())
            //{
            //    MessageBox.Show("Uruchom aplikację jeszczę raz z uprawnieniami administratora.");
            //}
        }

        public void SprobujZaladowacPlikZDomyslnejLokalizacji()
        {
            if (string.IsNullOrEmpty(_configHelper.ActualExcelPath)) return;
            try
            {
                ZaladujExcelaPoRazPierwszy(_configHelper.ActualExcelPath);
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
            }
        }

        public void AktualizujExcelLabel()
        {
            PathLabel.Content = _configHelper.ActualExcelPath.Length == 0 ? _excelNoPathLabel : _configHelper.ActualExcelPath;
        }

        public void LoadExcelWithCard(string path, string karta)
        {
            try
            {
                using (var pck = new ExcelPackage())
                {
                    using (var stream = File.OpenRead(path))
                    {
                        pck.Load(stream);
                    }
                    var ws = pck.Workbook.Worksheets[karta];
                    _dt = WorksheetToDataTable(ws, true);
                    _defaultView = CollectionViewSource.GetDefaultView(_dt.DefaultView);
                    ExcelGrid.ItemsSource = _defaultView;
                }
                _configHelper.PodmienUstawieniaSciezkiExcela(path);
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
                MessageBox.Show("Import failed. Original error: " + ex.Message);
            }
        }

        public void ZaladujExcelaPoRazPierwszy(string path)
        {
            try
            {
                using (var pck = new ExcelPackage())
                {
                    using (var stream = File.OpenRead(path))
                    {
                        pck.Load(stream);
                    }
                    var ws = pck.Workbook.Worksheets[1];
                    _excelCardNames.Clear();
                    ws.Workbook.Worksheets.ToList().ForEach(item =>
                    {
                        _excelCardNames.Add(item.ToString());
                    });
                    _dt = WorksheetToDataTable(ws);
                    _defaultView = CollectionViewSource.GetDefaultView(_dt.DefaultView);
                    ExcelGrid.ItemsSource = _defaultView;
                }
                _configHelper.PodmienUstawieniaSciezkiExcela(path);
                WypelnijNazwyKart();
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
                MessageBox.Show("Import failed. Original error: " + ex.Message);
            }
        }

        private DataTable WorksheetToDataTable(ExcelWorksheet ws, bool hasHeader = true)
        {
            _dt = new DataTable(ws.Name);
            try
            {
                int totalCols = ws.Dimension.End.Column;
                int totalRows = ws.Dimension.End.Row;
                int startRow = hasHeader ? 2 : 1;
                ExcelRange wsRow;
                DataRow dr;
                foreach (var firstRowCell in ws.Cells[1, 1, 1, totalCols])
                {
                    _dt.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }
                for (int rowNum = startRow; rowNum <= totalRows; rowNum++)
                {
                    wsRow = ws.Cells[rowNum, 1, rowNum, totalCols];
                    dr = _dt.NewRow();
                    foreach (var cell in wsRow)
                    {
                        dr[cell.Start.Column - 1] = cell.Text;
                    }
                    _dt.Rows.Add(dr);
                }

            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
            }
            return _dt;
        }

        private void UploadExcelFile(object sender, RoutedEventArgs e)
        {
            try
            {
                try
                {
                    _globalnyNumerZakladki = int.Parse("0");
                }
                catch
                {
                    _globalnyNumerZakladki = 0;
                    MessageBox.Show("Podaj poprawny numer zakładki.");
                    return;
                }
                var openfile = new OpenFileDialog
                {
                    DefaultExt = ".xlsx",
                    Filter = "(.xlsx)|*.xlsx"
                };
                var browsefile = openfile.ShowDialog();
                if (browsefile == true)
                {
                    PathLabel.Content = openfile.FileName.Trim();
                    ZaladujExcelaPoRazPierwszy(openfile.FileName);
                }
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
            }
        }

        public void WyslijEmaile()
        {
            try
            {
                DaneDoWyslania = ExcelGrid;
                SendEmailUsingOutlookTemplate();
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
            }
        }

        public void SynchroExcelData()
        {
            var excelPath = _configHelper.ActualExcelPath;
            var finfo = new FileInfo(excelPath);
            var dt2 = _dt.Copy();
            dt2.Columns.RemoveAt(0);
            try
            {
                if (File.Exists(excelPath))
                {
                    File.Delete(excelPath);
                }
                using (var pck = new ExcelPackage(finfo))
                {
                    var ws = pck.Workbook.Worksheets.Add("Dane");
                    ws.Cells["A1"].LoadFromDataTable(dt2, true);
                    pck.Save();
                }
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry("Blad przy zapisie danych excel" + ex.Message, DateTime.Now);
            }
        }

        private void BtnSearchClick(object sender, RoutedEventArgs e)
        {
            try
            {                
                const SearchingCriteria criteria = SearchingCriteria.Contains;
                const string operatorAkcji = " LIKE ";
                const string operatorZawiera1 = "%";
                const string operatorZawiera2 = "%";
                _defaultView = CollectionViewSource.GetDefaultView(_dt.DefaultView);

                var keywords = SearchTxtBox.Text.Trim().Split('|');
                var query = "";
                foreach (var item in keywords)
                {
                    var value = item;
                    if (value.Length == 0)
                    {
                        ExcelGrid.ItemsSource = _dt.DefaultView;
                    }
                    foreach (var column in _dt.Columns.Cast<DataColumn>().Where(column => column.ColumnName.Trim().Length != 0))
                    {
                        if (query.Length == 0)
                        {
                            query += "[" + column.ColumnName.Trim() + "] " + operatorAkcji + "'" + operatorZawiera1 + value.Trim() + operatorZawiera2 + "'";
                        }
                        else
                        {
                            query += " OR [" + column.ColumnName.Trim() + "] " + operatorAkcji + "'" + operatorZawiera1 + value.Trim() + operatorZawiera2 + "'";
                        }
                    }
                }
                try
                {
                    if (_dt.Select(query).Length == 0) return;
                    var filtered = _dt.Select(query).CopyToDataTable();
                    ExcelGrid.ItemsSource = filtered.DefaultView;
                }
                catch (Exception ex)
                {
                    _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
                    ExcelGrid.ItemsSource = _dt.DefaultView;
                }
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
            }
        }

        private void LoadDaiary(object sender, RoutedEventArgs e)
        {
            var daily = new Dziennik();
            daily.Show();
        }

        private void ZaznaczOdznaczGrid(bool isSelected)
        {
            try
            {
                ExcelGrid.SelectedItems.Clear();
                if (isSelected)
                {
                    foreach (var item in ExcelGrid.Items)
                    {
                        ExcelGrid.SelectedItems.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
            }
        }

        private void SelectUnselectButtnClick(object sender, RoutedEventArgs e)
        {
            if (PathLabel.Content.ToString() == _excelNoPathLabel) return;
            if (ZaznaczOdznacz.Label == "Zaznacz Wszystkie")
            {
                ZaznaczOdznaczGrid(true);
                ZaznaczOdznacz.Label = "Odznacz Wszystkie";
            }
            else
            {
                ZaznaczOdznaczGrid(false);
                ZaznaczOdznacz.Label = "Zaznacz Wszystkie";
            }
        }

        private void WypelnijNazwyKart()
        {
            CardNameExcelDropDown.Items.Clear();
            _excelCardNames.ForEach(cardName =>
            {
                CardNameExcelDropDown.Items.Add(cardName);
            });
            CardNameExcelDropDown.SelectedIndex = 0;
        }

        private void CardNameExcelDropDown_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            var actualPath = _configHelper.ActualExcelPath.Length == 0
                ? PathLabel.Content.ToString()
                : _configHelper.ActualExcelPath;
            LoadExcelWithCard(actualPath,
                   CardNameExcelDropDown.SelectedItem.ToString());
        }

        private void CloseAndSynchro(object sender, RoutedEventArgs e)
        {
            SynchroExcelData();
            Close();
        }
    
        private void CloseWindow(object sender, CancelEventArgs e)
        {
            if (PathLabel.Content.ToString() == _excelNoPathLabel) return;
            if (MessageBox.Show("Zsynchronizować Excel?",
                "Zamykanie",
                MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
            {
                SynchroExcelData();
            }
        }

        private void OutlookLinkClick(object sender, RoutedEventArgs e)
        {
            var fileDialog = new System.Windows.Forms.OpenFileDialog();
            if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                ExternalConfigHelper.OutlookPath = fileDialog.FileName;
//                _configHelper.PodmienUstawieniaSciezkiOutlook(fileDialog.FileName);
                WyslijEmaile();
            }
        }

        DataTable dt = new DataTable();
        Template _edytowanyTemplate { get; set; }
        Template tempTemplate { get; set; }
        public DataGrid DaneDoWyslania { get; set; }

        async private void SendEmailUsingOutlookTemplate()
        {
            try
            {
                if (!File.Exists(MailerSettings.GetOutlookFileName))
                {
                    MessageBox.Show("Brak dostępu do szablonu w podanej ścieżce.");
                    return;
                }
                var slownikWartosci = new Dictionary<string, string>();
                _coreRepo.GetDataGridRows(DaneDoWyslania);
                DataRowView rv;

                DaneDoWyslania.CanUserAddRows = false;
                DaneDoWyslania.BeginEdit();
                foreach (DataRowView item in DaneDoWyslania.SelectedItems)
                {
                    _edytowanyTemplate = tempTemplate;
                    for (int i = 1; i < DaneDoWyslania.Columns.Count; i++)
                    {
                        string kolumna = DaneDoWyslania.Columns[i].Header.ToString();
                        string wartosc = item.Row[i].ToString();
                        slownikWartosci[kolumna] = wartosc;
                    }
                    try
                    {
                        await Task.Factory.StartNew(() =>
                        {
                            _mailHelper.WyslijOutlookTemplate(slownikWartosci);
                        });
                    }
                    catch (Exception ex)
                    {
                        _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
                    }
                }
            }
            catch (Exception ex)
            {
                _lhelper.SaveNewDailyEntry(ex.Message, DateTime.Now);
            }
            finally
            {
                DaneDoWyslania.CanUserAddRows = true;
                DaneDoWyslania.EnableRowVirtualization = true;
            }
        }
    }
}
